use crate::app_error::AppError;
use crate::db::db_get;
use crate::dtos::files::{FileAddRequestQuick, FileDeleteRequest, FileDownloadRequest};
use crate::search_engine::{get_search_engine_file, SearchEngine};
use crate::utils::file_utils::{file_remove, file_write, get_file_path, hash_file};
use crate::{
    app_writer::AppResult,
    dtos::files::{FileAddRequest, FileResponse},
};

pub async fn add_file(req: FileAddRequest, data: Vec<u8>) -> AppResult<FileResponse> {
    let db = db_get()?;
    let hash = hash_file(&data);
    // 若存在相同文件，则直接添加关联
    if sqlx::query!(
        r#"
        SELECT file_hash FROM files WHERE file_hash = $1
    "#,
        hash
    )
    .fetch_optional(db)
    .await?
    .is_some()
    {
        return add_file_quick(FileAddRequestQuick {
            id: req.id,
            hash,
            file_name: req.file_name,
        })
        .await;
    }
    let mut tx = db.begin().await?;
    match {
        let _ = sqlx::query!(
            r#"
            INSERT INTO files (file_hash, file_type)
            VALUES ($1, $2)"#,
            hash,
            req.file_type
        )
        .execute(tx.as_mut())
        .await?;
        let _ = sqlx::query!(
            r#"
            INSERT INTO papers_files(paper_id, file_hash, file_name)
            VALUES ($1, $2, $3)
            "#,
            req.id,
            hash,
            req.file_name
        )
        .execute(tx.as_mut())
        .await?;
        Ok(())
    } {
        Ok(_) => {
            tx.commit().await?;
            file_write(&hash, &data).await?;
            let res = FileResponse {
                paper_id: req.id,
                file_name: req.file_name,
                file_hash: hash,
                file_type: req.file_type,
            };
            Ok(res)
        }
        Err(e) => {
            tx.rollback().await?;
            Err(e)
        }
    }
}

pub async fn add_file_quick(req: FileAddRequestQuick) -> AppResult<FileResponse> {
    let db = db_get()?;
    let mut tx = db.begin().await?;
    let hash = req.hash.to_ascii_lowercase();
    match {
        let rec = sqlx::query!(
            r#"
            SELECT file_type FROM files WHERE file_hash = $1
            "#,
            hash
        )
        .fetch_optional(tx.as_mut())
        .await?
        .ok_or(AppError::NoSuchRescource("file"))?;
        let _ = sqlx::query!(
            r#"
            INSERT INTO papers_files(paper_id, file_hash, file_name)
            VALUES ($1, $2, $3)
            "#,
            req.id,
            hash,
            req.file_name
        )
        .execute(tx.as_mut())
        .await?;
        Ok(rec.file_type)
    } {
        Ok(file_type) => {
            tx.commit().await?;
            let res = FileResponse {
                paper_id: req.id,
                file_name: req.file_name,
                file_hash: hash,
                file_type,
            };
            Ok(res)
        }
        Err(e) => {
            tx.rollback().await?;
            Err(e)
        }
    }
}

pub async fn files(paper_id: &str) -> AppResult<Vec<FileResponse>> {
    let db = db_get()?;
    let res = sqlx::query_as!(
        FileResponse,
        r#"
        SELECT papers_files.paper_id, papers_files.file_name, files.file_hash, files.file_type
        FROM papers_files
        INNER JOIN files ON papers_files.file_hash = files.file_hash
        WHERE papers_files.paper_id = $1
    "#,
        paper_id
    )
    .fetch_all(db)
    .await?;
    Ok(res)
}

pub async fn get_file_info_by_userid(user_id: &str, file_hash: &str) -> AppResult<FileResponse> {
    let db = db_get()?;
    let res = sqlx::query_as!(
        FileResponse,
        r#"
        SELECT papers_files.paper_id, papers_files.file_name, files.file_hash, files.file_type
        FROM papers_files
        INNER JOIN files ON papers_files.file_hash = files.file_hash
        INNER JOIN users_papers ON papers_files.paper_id = users_papers.paper_id
        WHERE users_papers.user_id = $1 AND files.file_hash = $2
    "#,
        user_id,
        file_hash
    )
    .fetch_one(db)
    .await?;
    Ok(res)
}

pub async fn download_file(req: FileDownloadRequest) -> AppResult<(String, String)> {
    let db = db_get()?;
    let hash = req.hash.to_ascii_lowercase();
    let file_name = sqlx::query!(
        r#"SELECT papers_files.file_name FROM files
        INNER JOIN papers_files ON files.file_hash = papers_files.file_hash
        WHERE papers_files.paper_id = $1 AND files.file_hash = $2"#,
        req.id,
        hash
    )
    .fetch_optional(db)
    .await?
    .ok_or(AppError::NoSuchRescource("file"))?
    .file_name;
    let file_path = get_file_path(&hash);
    Ok((file_name, file_path))
}

pub async fn delete_file(req: FileDeleteRequest) -> AppResult<()> {
    let db = db_get()?;
    let _ = sqlx::query!(
        r#"
            DELETE FROM papers_files 
            WHERE paper_id = $1 AND file_hash = $2
            "#,
        req.id,
        req.hash,
    )
    .execute(db)
    .await?;
    Ok(())
}

pub async fn clean_up() -> AppResult<()> {
    let db = db_get()?;
    let rec = sqlx::query!(
        r#"
            DELETE FROM files 
            WHERE file_hash NOT IN (SELECT file_hash FROM papers_files)
            RETURNING file_hash
            "#,
    )
    .fetch_all(db)
    .await?;

    let search_engine = get_search_engine_file().await?;

    for row in &rec {
        let _ = file_remove(&row.file_hash).await;
        search_engine.delete_file(&row.file_hash).await;
    }
    if !rec.is_empty() {
        search_engine.commit().await?;
    }
    Ok(())
}
